﻿using MySql.Data.MySqlClient;
using System;
using System.Text;
namespace QWManage.DAL
{


    public class MySqlToSqlServerConverter
    {
        public static string GetCreateTableSql(string mySqlConnectionString, string mySqlTableName, string newTableName)
        {
            // 连接到MySQL  
            using (MySqlConnection mySqlConnection = new MySqlConnection(mySqlConnectionString))
            {
                mySqlConnection.Open();

                // 查询MySQL的元数据  
                string query = $"SELECT " +
                    $"COLUMN_NAME, " +
                    $"DATA_TYPE, " +
                    $"CHARACTER_MAXIMUM_LENGTH, " +
                    $"IS_NULLABLE " +
                    $"FROM INFORMATION_SCHEMA.COLUMNS " +
                    $"WHERE TABLE_NAME = '{mySqlTableName}'";

                using (MySqlCommand mySqlCommand = new MySqlCommand(query, mySqlConnection))
                {
                    using (MySqlDataReader reader = mySqlCommand.ExecuteReader())
                    {
                        StringBuilder sql = new StringBuilder();
                        sql.AppendLine($"CREATE TABLE [dbo].[{newTableName}]");
                        sql.AppendLine("(");
                        sql.AppendLine("[Owner] [nvarchar](50),");
                        while (reader.Read())
                        {

                            string column_name = (string)reader["COLUMN_NAME"];
                            string data_type_mysql = (string)reader["DATA_TYPE"];
                            int? max_length = null;
                            if (reader["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value)
                            {
                                max_length = (int)(ulong)reader["CHARACTER_MAXIMUM_LENGTH"];
                            }
                            string data_type_sql = MapMySqlDataTypeToSqlServer(data_type_mysql, max_length);
                            bool isNullable = ((string)reader["IS_NULLABLE"]).ToLower() == "yes"; // IS_NULLABLE returns a boolean for NULL constraints  

                            string line = $"[{column_name}] {data_type_sql}";
                            if (!isNullable) line += " NOT NULL";
                            line += ",";
                            sql.AppendLine(line);
                        }

                        // Remove the trailing comma and close the CREATE TABLE statement  
                        sql.Length -= 1; // Remove the last comma  
                        sql.AppendLine(")");

                        return sql.ToString();
                    }
                }
            }
        }

        private static string MapMySqlDataTypeToSqlServer(string data_type_mysql, int? max_length)
        {
            switch (data_type_mysql)
            {
                case "int": return "[int]";
                case "varchar": return "[nvarchar](" + max_length.Value + ")";
                case "datetime": return "[datetime]";
                case "bigint": return "[bigint]";
                case "decimal": return "[decimal](18,2)";
                default: throw new Exception("unexpected filed：" + data_type_mysql);
            }
        }

        public static string GetClassInfo(string mySqlConnectionString, string mySqlTableName)
        {
            // 连接到MySQL  
            using (MySqlConnection mySqlConnection = new MySqlConnection(mySqlConnectionString))
            {
                mySqlConnection.Open();

                // 查询MySQL的元数据  
                string query = $"SELECT " +
                    $"COLUMN_NAME, " +
                    $"DATA_TYPE, " +
                    $"CHARACTER_MAXIMUM_LENGTH, " +
                    $"IS_NULLABLE " +
                    $"FROM INFORMATION_SCHEMA.COLUMNS " +
                    $"WHERE TABLE_NAME = '{mySqlTableName}'";

                using (MySqlCommand mySqlCommand = new MySqlCommand(query, mySqlConnection))
                {
                    using (MySqlDataReader reader = mySqlCommand.ExecuteReader())
                    {
                        StringBuilder class_info = new StringBuilder();
                        while (reader.Read())
                        {
                            string line = "public ";
                            string column_name = (string)reader["COLUMN_NAME"];
                            string data_type_mysql = (string)reader["DATA_TYPE"];
                            bool isNullable = ((string)reader["IS_NULLABLE"]).ToLower() == "yes"; // IS_NULLABLE returns a boolean for NULL constraints  
                            string suffix = isNullable ? "?" : "";
                            switch (data_type_mysql)
                            {
                                case "int": line += "int" + suffix + " " + column_name + "{set;get;}"; break;
                                case "varchar": line += "string" + " " + column_name + "{set;get;}"; break;
                                case "datetime": line += "DateTime" + suffix + " " + column_name + "{set;get;}"; break;
                                case "bigint": line += "long" + suffix + " " + column_name + "{set;get;}"; break;
                                case "decimal": line += "decimal" + suffix + " " + column_name + "{set;get;}"; break;
                                default: throw new Exception("unexpected filed：" + data_type_mysql);
                            }

                            class_info.AppendLine(line);
                        }

                        // Remove the trailing comma and close the CREATE TABLE statement  
                        return class_info.ToString();
                    }
                }
            }
        }
    }
}
